#!/bin/sh
#
# OracleAppMount.sh: shell script to automate cloning and bring up an Oracle database using backup image copies
#
# usage: OracleAppMount.sh <New database sid name> <Oracle Home path> <Data Dir path> <Backup location mount path>"
nsid=$1
oracleHome=$2
backupLocation=$3
dataDir=$backupLocation

scriptsRoot=${LB_ROOT}
if [ -z "$scriptsRoot" ];then
    echo "missing env LB_ROOT"
    exit 1
fi

. ${scriptsRoot}/oracle/oracleclone/OracleAppMountComFunc.sh

BASEDIR=${scriptsRoot}/oracle/oracleclone
#echo $BASEDIR
cd $BASEDIR

if [ "$#" -ne 3 ]; then
 echo "This script needs 3 input parameter"
 echo "usage: OracleAppMount.sh <New database sid name> <Oracle Home path> <Backup location mount path>"
 echo " For example: OracleAppMount.sh clonedb /home/oracle/app/oracle/product/11.2.0/dbhome_1 /act/mnt/Job_0060901_mountpoint_1341234106825"
exit 1
fi

#logger for error messages
errMsgFile=$nsid"_errMsg"

function apmlogger {
     errmsg="$1"
     if [ ! -z "$errmsg" ]; then
        echo "$errmsg" > $errMsgFile
     fi
}

function apmappend {
     errmsg="$1"
     echo " $errmsg" >> $errMsgFile
}

function geterrmsg {
     ecode=$1
     if [ -z "$ecode" ]; then
        echo "missing error code when retrieving error message"
     fi
     msgrepo=${scriptsRoot}/oracle/apmerrmsg.sh
     msg=`grep $ecode"=" $msgrepo | awk -F'=' '{print $NF}'`
     if [ ! -z "$msg" ]; then
        echo "$msg"
     else
        echo "failed to retrieve error message for $ecode"
     fi
}

function logerrormsg {
     ecode=$1
     appender=$2

     emsg=`geterrmsg $ecode`
     apmlogger "$emsg"
     if [ ! -z "$$appender" ]; then
        apmappend $appender
     fi
     echo `$tdate`$nsid": $emsg $appender"
}
#logger

if [ ! -z "$isrestore" ] && [ "$isrestore" = "true" ]; then
ORACLE_HOME=$oracleHome
export ORACLE_HOME
PATH=$ORAHOME/bin:$PATH
export PATH
ORACLE_SID=$nsid
export ORACLE_SID
TNS_ADMIN=$tnsadmindir
export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
set heading off
set feedback off
startup mount;
alter database end backup;
alter database open;
exit;
EOF

if [ $? -ne 0 ]; then
   logerrormsg EM_END_HOT_BACKUP $nsid
   exit 1
fi

exit 0
#inband restore
fi

# correctly set the ORACLE_BASE AND ORACLE_HOME  for your environment
export ORACLE_HOME=$oracleHome
export ORACLE_BASE=$dataDir
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

if [ ! -d $ORACLE_HOME ]; then
   logerrormsg EM_ORACLE_HOME
   exit 1
fi

if [ ! -d  $ORACLE_BASE ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION $ORACLE_BASE
   exit 1
fi

if [ ! -d $backupLocation ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION $backupLocation
   exit 1
fi

sqlplus=$ORACLE_HOME/bin/sqlplus

psid=`get_teardown_sid $dataDir`
#remountflag=$?
#if [ $remountflag = 0 ]  && [ "$ischild" = "false" ]; then
remountflag=$isremount
if [ "$remountflag" = "true" ]; then
# remount of previously mounted and teared down instance
    echo "Remount $psid"
    ${scriptsRoot}/oracle/oracleclone/OracleAppRemount.sh $nsid $ORACLE_HOME $dataDir
    if [ $? != 0 ]; then
       logerrormsg EM_REMOUNT "$psid to $nsid"
       exit 1
    fi
else
# first time AppMount
# get the last archivelog name with path
mname=$backupLocation
echo $mname

# get the production sid name from mount
getpname=`ls -ltr $mname | grep '__backup.ora'`
pname=`echo $getpname | awk '{print $NF}'`
osid=`echo $pname | awk -F"__" '{print $(NF-1)}'`
echo $osid
osid1=$osid"1"
osid2=$osid"2"
osid3=$osid"3"
osid4=$osid"4"
osid5=$osid"5"
osid6=$osid"6"
echo $osid1
echo $osid2

temporafile=$mname/$osid"__backup_temp.ora"
orafile=$mname/$osid"__backup.ora"
cp $orafile $orafile".orig"
echo $orafile
backupdir=$mname/datafile
echo $backupdir

dbfsPath=$mname/$osid"_datafiles_readonly_catalog.conf"
echo "read only tablespace file path : $dbfsPath"

# keep mountpoint with the mount for remount
echo "$backupLocation" > $backupLocation/pmpkeep

openDatabase="openDatabase_"$nsid".sql"
openDBlog="openDBlog_"$nsid".txt" 
openPDatabase="openPDatabase_"$nsid".sql"
openTmpDatabase="openTmpDatabase_"$nsid".sql"
openTmpDatabase1="openTmpDatabase1_"$nsid".sql"
addUnnamed="addUnnamed_"$nsid".sql"
tabfilelist="tabfilelist_"$nsid
undofilelist="undofilelist_"$nsid".sql"
offlinefilelist="offlinefilelist_"$nsid
pluggablelist="pluggablelist_"$nsid
pglist="pglist_"$nsid
createRedolog="createRedolog_"$nsid".sql"
openFullDatabase="openFullDatabase_"$nsid".sql"
CLONE_CRT_SQL="Clone_createCtrlFile_"$nsid"_g.sql"
verifyDatabase="verifyDatabase.sql"

#sqlplus=$ORACLE_HOME/bin/sqlplus

if [ ! -d $mname ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION
   exit 1
fi

if [ -z "$nsid" ]; then
   logerrormsg EM_NEW_SID
   exit 1
fi

if [ -z "$orafile" ]; then
   logerrormsg EM_INIT_FILE
   exit 1
fi

if [ -z "$backupdir" ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION
   exit 1
fi

oobase=$ORACLE_BASE 
# replace backed up SID with new SID
#sed -i "s/$osid/$nsid/g" $orafile
sed "/$osid2/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid3/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid4/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid5/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid6/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/__/d" $orafile > $temporafile
mv $temporafile $orafile

sed "s/'$osid'/'$nsid'/g" $orafile > $temporafile
mv $temporafile $orafile

# make required directory structures
mkdir -p $ORACLE_BASE/oradata/$nsid
mkdir -p $ORACLE_BASE/flash_recovery_area/$nsid
mkdir -p $ORACLE_BASE/admin/$nsid/adump
mkdir -p $ORACLE_BASE/admin/$nsid/dpdump
mkdir -p $ORACLE_BASE/admin/$nsid/control
mkdir -p $ORACLE_BASE/admin/$nsid/archivelog

controlfile="*.control_files='$ORACLE_BASE/admin/$nsid/control/control01.ctl','$ORACLE_BASE/admin/$nsid/control/control02.ctl'"
dbcreatefile="*.db_create_file_dest='$ORACLE_BASE/oradata/$nsid'"
dbrecoveryfile="*.db_recovery_file_dest='$ORACLE_BASE/flash_recovery_area/$nsid'"
if [ -z "$diagnostic_dest" ]; then
  mkdir -p $ORACLE_HOME/../../admin/$nsid/adump
  mkdir -p $ORACLE_HOME/../../diag
else
  mkdir -p $diagnostic_dest/$nsid
  mkdir -p $diagnostic_dest/$nsid/admin/adump
fi
logarchivedest="*.log_archive_dest_1='LOCATION=$ORACLE_BASE/admin/$nsid/archivelog/'"

nsidlen=`echo -n $nsid | wc -m`
if [ $nsidlen -gt 8 ]; then
newdb_name=`echo $nsid | cut -c1-8`
db_unique_name=$nsid
else
newdb_name=$nsid
fi

dbname="*.db_name='$newdb_name'"
dbuniquename="*.db_unique_name='$nsid'"

jqprocess="*.job_queue_processes=0"

if [ -z "$totalmemory" ]; then
    #sgatarget="*.sga_target=800m"
    #pgatarget="*.pga_aggregate_target=400m"
    echo " memory size is not defined "
    echo "source memory setup will be used"
else
    if [ ! -z "$totalmemory" ] && [ -z "$sgapct" ]; then
        echo " SGA is not defined "
        sed '/sga_max_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/sga_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/pga_aggregate_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_max_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/streams_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_keep_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/large_pool_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        totalmemoryM=$totalmemory"m"
        mtarget="*.memory_target=$totalmemoryM"
        echo "$mtarget" >> $orafile
    else
        echo " total memory and SGA is defined "
        echo "sga and pga setup will be used"
        sed '/sga_max_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/sga_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/pga_aggregate_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_max_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/streams_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_keep_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/large_pool_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sgc=$(($totalmemory*$sgapct/100))
        sgam=$sgc"m"
        sgatarget="*.sga_target=$sgam"
        pgc=$(($totalmemory - $sgc ))
        pgam=$pgc"m"
        pgatarget="*.pga_aggregate_target=$pgam"
        echo "$sgatarget" >> $orafile
        echo "$pgatarget" >> $orafile

        lps=$((($sgc*10)/100))
        if [ "$lps" -gt "200" ]; then
           lapsm=$lps"m"
        else
           lapsm=200m
        fi

        largepool="*.large_pool_size=$lapsm"
        echo "$largepool" >> $orafile

        if [ ! -z "$shared_pool_size" ]; then
            sps=$shared_pool_size"m"
            sharedpoolsize="*.shared_pool_size=$sps"
            echo "$sharedpoolsize" >> $orafile
        fi

        if [ ! -z "$db_cache_size" ]; then
           dcs=$db_cache_size"m"
           dbcachesize="*.db_cache_size=$dcs"
           echo "$dbcachesize" >> $orafile
        fi
    fi
 fi

sed '/db_recovery_file_dest_size=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ ! -z "$db_recovery_file_dest_size" ]; then
drfds=$db_recovery_file_dest_size"m"
dbrecoveryfilesize="*.db_recovery_file_dest_size=$drfds"
else
dbrecoveryfilesize="*.db_recovery_file_dest_size=50g"
fi

sed '/processes=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ ! -z "$processes" ]; then
process="*.processes=$processes"
else
process="*.processes=500"
fi

if [ ! -z "$open_cursors" ]; then
sed "/open_cursors=/d" $orafile > $temporafile
mv $temporafile $orafile
opencursors="*.open_cursors=$open_cursors"
echo "$opencursors" >> $orafile
fi

sed "/control_files=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/audit_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_create_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_recovery_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/diagnostic_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/log_archive_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/LOG_ARCHIVE_DEST/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/cluster_database=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/remote_listener=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/background_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/core_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/user_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/local_listener=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_name=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/dispatchers=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/instance_number=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/thread=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_unique_name=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/resource_manager_plan/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/thread=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/THREAD=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/instance_number=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/undo_tablespace=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/__/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/service_names=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_create_online_log_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/SPFILE=/d" $orafile > $temporafile
mv $temporafile $orafile

sed  "/dg_broker_config_file/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/dg_broker_start/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/fal_client/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/fal_server/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_config/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_trace/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_min_succeed_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_file_name_convert/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/standby_file_management/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/db_file_name_convert/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/archive_lag_target/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_trace/d" $orafile > $temporafile
mv $temporafile $orafile

sed  "/cluster_interconnects/d" $orafile > $temporafile
mv $temporafile $orafile
sed '/_diag_adr_trace_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/cluster_database_instances/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/instance_name/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/standby_archive_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/background_dump_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/_fix_control/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/event/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/use_large_pages/d' $orafile > $temporafile
mv $temporafile $orafile

echo "$dbname" >> $orafile
echo "$dbuniquename" >> $orafile
echo "$controlfile" >> $orafile
echo "$dbcreatefile" >> $orafile
echo "$dbrecoveryfile" >> $orafile
echo "$dbrecoveryfilesize" >> $orafile
if [ ! -z "$diagnostic_dest" ]; then
 dbdiagnostic="*.diagnostic_dest='$diagnostic_dest'"
 echo "$dbdiagnostic" >> $orafile
 adest="*.audit_file_dest='$diagnostic_dest/$nsid/admin/adump'"
 echo "$adest" >> $orafile
else
 auditfile="*.audit_file_dest='$ORACLE_BASE/admin/$nsid/adump'"
 echo "$auditfile" >> $orafile
fi
#echo "$dbdiagnostic" >> $orafile
echo "$logarchivedest" >> $orafile
echo "$process" >> $orafile
echo "$jqprocess" >> $orafile

sed '/disk_asynch_io/d' $orafile > $temporafile
mv $temporafile $orafile
diskasyncio="*.disk_asynch_io='TRUE'"
echo "$diskasyncio" >> $orafile

# generate a controlfile creation script
./Clone_generateCtrlFileScript.sh $nsid $backupdir $ORACLE_BASE"/oradata"

if [ $? -ne 0 ]; then
   logerrormsg EM_GENERATE_CONTROLFILE
   exit 1
fi

#create sql file to open the database and add tempfile
if [ -e $openDatabase ]; then
   echo "removing $openDatabase before generating a new one"
   rm -f $openDatabase
fi

if [ -e $openDatabase1 ]; then
   echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi

if [ -e $recoverDatabase ]; then
   echo "removing $recoverDatabase before generating a new one"
   rm -f $recoverDatabase
fi

if [ -e $addUnnamed ]; then
   echo "removing $addUnnamed "
   rm -f $addUnnamed
fi

if [ -e $tabfilelist ]; then
   #echo "removing $tabfilelist before generating a new one"
   rm -f $tabfilelist
fi

if [ -e $undofilelist ]; then
  # echo "removing $undofilelist "
   rm -f $undofilelist
fi

if [ -e $openDBlog ]; then
  # echo "removing $openDBlog "
   rm -f $openDBlog
fi

if [ -e $openFullDatabase ]; then
    #echo "removing $openFullDatabase before generating a new one"
    rm -f $openFullDatabase
fi

# now export new SID, do this outside of shell, before invoke this shell
export ORACLE_SID=$nsid

# copy ora file
#cp $orafile $ORACLE_HOME/dbs/init"$nsid".ora
initf=$ORACLE_HOME/dbs/init"$nsid".ora
spf=$ORACLE_HOME/dbs/spfile"$nsid".ora

# keep a copy of ora file under ORACLE_BASE
cp $orafile $ORACLE_BASE/init"$nsid".ora
# start the database for cloning
$sqlplus / as sysdba @Clone.sql $orafile $spf

if [ $? -ne 0 ]; then
   logerrormsg EM_GENERATE_CONTROLFILE
   exit 1
fi

echo 'SPFILE=$spf' > $initf

$sqlplus / as sysdba @mountDatabase.sql $CLONE_CRT_SQL

 
if [ $? -ne 0 ]; then
   logerrormsg EM_CREATE_CONTROLFILE
   exit 1   
fi

if [ -e $CLONE_CRT_SQL ]; then
   rm -f $CLONE_CRT_SQL
fi

dbrecover="dbrecover_"$nsid".txt"
export ORACLE_SID=$nsid

echo " recovering database: pdbcnt=$pdbcnt ischild=$ischild " > $dbrecover

#if [ "$pdbcnt" -gt "0" ]; then
$sqlplus / as sysdba << EOF
alter database archivelog;
exit;
EOF
#fi

./recoverRman.sh $mname $nsid $dbfsPath

retval=0
recoverdb=true
if [ ! -z "$rrecovery" ] && [ "$rrecovery" = "false" ]; then
   recoverdb=false
fi

if [ "$recoverdb" = "true" ]; then
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openDatabase
echo "alter database open resetlogs;" >> $openDatabase
echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$dataDir/datafile/temp_cl01.dbf' size 5g autoextend on;" >> $openDatabase
echo "exit" >> $openDatabase

echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openFullDatabase
echo "alter database open;" >> $openFullDatabase
echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$ORACLE_BASE/oradata/$nsid/temp_cl01.dbf' size 5g autoextend on;" >> $openFullDatabase
echo "exit" >> $openFullDatabase
 
vnum=0
vnum=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '12%';
exit;
EOF1`
echo "********************* vnum=$vnum ***********"
pdbcnt=0
if [ "$vnum" -gt "0" ]; then
    pdbcnt=`$sqlplus -S / as sysdba << 'EOF1'
    set head off;
    set feedback off
    select count(*) from v\$pdbs where name like 'PDB%SEED';
    exit;
EOF1`
fi
export vnum
export pdbcnt
 
openDBlog="openDBlog_"$nsid".txt"

if [ -e $openDBlog ]; then
#   echo "removing $openDBlog before generating a new one"
    rm -f $openDBlog
fi

echo "Opening Database is started..." > $openDBlog

if [ "$pdbcnt" -gt "0" ]; then
$sqlplus / as sysdba @recoverPDBSeed.sql >> $openDBlog
fi

export ORACLE_SID=$nsid
$sqlplus / as sysdba @$openDatabase >> $openDBlog
retval1=$?

echo "*********************** retval:$retval1 **************************"

orabug=`grep ORA-01092 $openDBlog`
if [ -z "$orabug" ]; then
 orabug=`grep ORA-03113 $openDBlog`
 fi

if [ ! -z "$orabug" ]; then
 sleep 60
 $ORACLE_HOME/bin/rman target / log $dbrecover append << EOF
 run {
  startup mount;
  recover database;
  }
  exit;
  EOF
  $sqlplus / as sysdba @$openDatabase >> $openDBlog
  oraopen=`grep ORA-01139 $openDBlog`
  if [ ! -z "$oraopen" ]; then
  $sqlplus / as sysdba @$openFullDatabase >> $openDBlog
  fi
  retval1=$?
fi

if [ "$retval1" -gt "0" ]
then
 while [ "$retval1" -gt "0" ]; do

undfcnt=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
select count(*) from v\$datafile where name like '%/UNNAMED%';
exit;
EOF1`

  if [ "$undfcnt" -gt "0" ]; then

      undfnum=`$sqlplus -S / as sysdba << 'EOF2'
        set head off;
        select file# from v\$datafile where name like '%/UNNAMED%';
        exit;
EOF2`

        echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $addUnnamed
        echo "alter database create datafile $undfnum as new;" >> $addUnnamed
        echo  "exit" >> $addUnnamed
#        chmod 777 $addUnnamed

        $sqlplus / as sysdba @addUnnamed.sql
        ./recoverRman.sh $mname $nsid $dbfsPath
        $sqlplus / as sysdba @$openDatabase >> $openDBlog
   fi
retval1=$?;
 done
fi

oraissue=`grep ORA-01196 $openDBlog`
if [ ! -z "$oraissue" ]; then
    logerrormsg EM_DB_OPEN "$oraissue"
    exit 1
    #exit $oraissue
fi
 
ggusername=`$sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none;
select distinct owner from dba_segments where segment_name like 'GGS_%' and owner<>'SYS' and owner<>'SYSTEM' and rownum<2;
exit;
EOF1`
 
if [ ! -z "$ggusername" ]; then
 echo "Golden Gate User:$ggusername"
 $sqlplus -S / as sysdba <<EOF1
 WHENEVER SQLERROR EXIT SQL.SQLCODE
 alter user "$ggusername" account lock;
 startup force restrict;
 drop user "$ggusername" cascade;
 exit;
 EOF1
 retval=$?
 echo "Drop User: $retval"
 
if [ "$retval" -gt "0" ]; then
 $sqlplus -S / as sysdba <<EOF1
 WHENEVER SQLERROR EXIT SQL.SQLCODE
 shutdown abort;
 startup force restrict;
 drop user "$ggusername" cascade;
 exit;
 EOF1
 fi
fi
 
opencnt=`$sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?
if [ "$retval" -gt "0" ]; then
 $sqlplus / as sysdba << EOF
 startup force restrict;
 exit;
 EOF
fi
 
if [ "$pdbcnt" -gt "0" ]; then
 $ORACLE_HOME/bin/rman target / log $dbrecover append << 'EOF'
 run {
 ALTER pluggable database all open;
 alter session set "_oracle_script"=true;
 shutdown immediate;
 startup;
 ALTER pluggable database all open;
 alter pluggable database all save state;
 }
 exit;
 EOF
fi
 
opencnt=`$sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ "$retval" -gt "0" ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
  exit 1;
elif [ "$opencnt" -eq "0" ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
 exit 1;
fi

tempg=0
tempg=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
set feedback off
select count(*) from dba_tablespace_groups;
exit;
EOF1`

if [ "$tempg" -gt "0" ]; then
tempgn=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
set feed off;
set echo off;
select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE');
exit;
EOF1`
fi

tabgroup="tabgroup_"$nsid".txt"

if [ "x$tempgn" != "x" ]; then
$sqlplus -S / as sysdba << 'EOF1' > $tabgroup
set head off;
set feed off;
set echo off;
select tablespace_name from dba_tablespace_groups where group_name in (select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'));
exit;
EOF1
chmod 755 $tabgroup
else
tempn=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
select property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
exit;
EOF1`
fi

if [ "$pdbcnt" -eq "0" ]; then
$sqlplus -S / as sysdba << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and ts# not in (select ts# from v\$datafile);
exit;
EOF
else
$sqlplus -S / as sysdba << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED') and ts# not in (select ts# from v\$datafile where con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED'));
exit;
EOF
fi

echo "alter database default temporary tablespace TEMP_CL;" > $openDatabase1

while read line
do
    set -- $line
    tabname=$1
        echo "outside if $tabname"
        if [ "x$tabname" != "x" ]; then
        echo "inside if $tabname"
        tmpstr1="$tabname""_01.dbf"
        tmpstr=" '$dataDir/datafile/$tmpstr1' size 5g autoextend on;"
        echo "drop tablespace $tabname including contents and datafiles;" >> $openDatabase1
        echo "create temporary tablespace $tabname tempfile $tmpstr" >> $openDatabase1
        fi
done < $tabfilelist

echo "shutdown immediate;" >> $openDatabase1
echo  "startup;" >> $openDatabase1

if [ "x$tempgn" != "x" ]; then
while read line
do
    set -- $line
    tabgname=$1
        echo "tab group if $tabgname"
        if [ "x$tabgname" != "x" ]; then
        echo "in tab group if $tabgname"
        echo "alter tablespace $tabgname tablespace group $tempgn;" >> $openDatabase1
        fi
done < $tabgroup

echo "alter database default temporary tablespace $tempgn;" >> $openDatabase1
else
echo "alter database default temporary tablespace $tempn;" >> $openDatabase1
fi

echo  "exit" >> $openDatabase1

if [ -e $dbrecover ]; then
   echo "removing $dbrecover "
#   rm -f $dbrecover
fi

runnidsql="runnidrotbs"_$nsid".sql"
if [ "$nonid" != "true" ]; then
if [ ! -f "$dbfsPath" ]; then
   roTBS='NONE'
else
   roTBS=`cat $dbfsPath`
fi

roCTBS=''
for i in $(echo $roTBS | tr "," "\n")
do
    echo "$i"
    if [ -z "$roCTBS" ]; then
	roCTBS="'''"${i}"'''"
    else
	roCTBS=${roCTBS}"\n'''"${i}"'''"
    fi
done

echo "$roCTBS"

ctbs_input=''
echo "$roCTBS" > roCTBS_output.txt
ctbs_input=`cat roCTBS_output.txt`
echo "$ctbs_input"

runnidsql="runnidrotbs"_$nsid".sql"
if [ -e "$runnidsql" ]; then
rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname ONLINE" >> $runnidsql
        echo "@nidROTBS.sql $readtabname 'READ WRITE'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql

$sqlplus / as sysdba << EOF
@$runnidsql
shutdown immediate;
startup mount;
exit;
EOF
change_dbid $nsid $ORACLE_HOME

if [ ! -f "$runnidsql" ]; then
 rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname 'READ ONLY'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql


$sqlplus / as sysdba << EOF
startup mount;
alter database open resetlogs;
@$runnidsql
exit;
EOF
fi

if [ $? -ne 0 ]; then
   logerrormsg EM_NID
   exit 1
fi

if [ ! -f "roCTBS_output.txt" ]; then
 rm -f roCTBS_output.txt
fi

export ORACLE_SID=$nsid
$sqlplus / as sysdba @$openDatabase1 >> $openDBlog
retval=$?

if [ -e $openDatabase ]; then
   echo "removing $openDatabase "
   rm -f $openDatabase
fi

if [ -e $openDatabase1 ]; then
   echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi

if [ -e $verifyDatabase ]; then
   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi

if [ -e $addUnnamed ]; then
   echo "removing $addUnnamed "
   rm -f $addUnnamed
fi

if [ -e $openDBlog ]; then
   echo "removing $openDBlog "
#   rm -f $openDBlog
fi

if [ -e $openFullDatabase ]; then
   echo "removing $openFullDatabase "
   rm -f $openFullDatabase
fi

export ORACLE_SID=$nsid
$sqlplus / as sysdba  << EOF3
drop tablespace TEMP_CL including contents and datafiles;
exit;
EOF3

opencnt=`$sqlplus -S / as sysdba  << 'EOF1'
set head off;
set feedback off
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`

if [ "$opencnt" -eq "0" ]; then
   logerrormsg EM_DB_READ_WRITE
   exit 1;
fi

if [ "$pdbcnt" -gt "0" ]; then
$sqlplus / as sysdba << EOF
ALTER pluggable database all open instances=all;
alter pluggable database all save state instances=all;
exit;
EOF
fi

if [ "$pdbcnt" -gt "0" ]; then
pdbname=`$sqlplus -S / as sysdba << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select ltrim(rtrim(listagg(to_char(name),',') WITHIN GROUP (ORDER BY name))) from v\$pdbs where name not like 'PDB%SEED';
exit;
EOF1`

for x in $(echo $pdbname | tr "," "\n")
do
mkdir -p $ORACLE_BASE/oradata/$nsid/$x
$sqlplus / as sysdba << EOF
@tmpPDB.sql "$x" "$ORACLE_BASE/oradata/$nsid/$x"
EOF
done
fi

echo "set pagesize 100" >> $verifyDatabase
echo "set linesize 100" >> $verifyDatabase
echo "set echo off" >> $verifyDatabase
echo "set feedback off" >> $verifyDatabase
echo "set wrap on" >> $verifyDatabase
echo "col tablespace_name format a15" >> $verifyDatabase
echo "col file_name format a75" >> $verifyDatabase
echo "select distinct tablespace_name, file_name from dba_data_files order by 1;" >> $verifyDatabase
echo "select owner,count(*) from dba_tables group by owner order by 1;" >> $verifyDatabase
echo "select dbid, name, open_mode from v\$database;" >> $verifyDatabase
echo "select sum(bytes)/(1024*1024*1024) \"dbSize (GB)\" from dba_data_files;" >> $verifyDatabase
echo "select * from v\$instance;" >> $verifyDatabase
echo "exit" >> $verifyDatabase
fi

setdb="setdb_"$nsid
echo "# Modify the following variables based on the environment" > $setdb
echo "export ORACLE_SID=$nsid" >> $setdb
echo "export ORACLE_HOME=$ORACLE_HOME" >> $setdb
echo " " >> $setdb
echo "export PATH=$ORACLE_HOME/bin:$PATH" >> $setdb
echo "#END OF FILE" >> $setdb

cp $setdb $dataDir

export ORACLE_SID=$nsid
if [ "$retval" -gt "0" ]; then
echo "********** error cloning database ***************"
fi

if [ -z "$hostip" ]; then
   hostip="localhost"
fi

# update tnsnames.ora
#./updateTNSNames.sh $nsid $ORACLE_HOME $hostip
export ORACLE_SID=$nsid
vnum=0
vnum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '10%';
exit;
EOF1`

set +x
syspwd="change_on_install"
if [ ! -z "$ouser" ] && [ "$ouser" = "sys" ] && [ ! -z "$opass" ]; then
   syspwd=$opass
fi

export ORACLE_SID=$nsid
# create pwfile
if [ "$vnum" -gt "0" ]; then
#orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=change_on_install ignorecase=yes
  orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=$syspwd 
else
  orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=$syspwd ignorecase=yes
fi

# update /etc/oratab for reprotect
oratabentry=$nsid":"$oracleHome":Y"
echo "$oratabentry" >> /etc/oratab
fi

if [ "$recoverdb" = "true" ]; then
# grant user sysdba privilege
if [ ! -z "$ouser" ] && [ "$ouser" != "sys" ]; then
$sqlplus / as sysdba << EOF
grant sysdba to $ouser;
exit;
EOF
fi

cp $ORACLE_HOME/dbs/orapw$nsid $dataDir

$sqlplus / as sysdba << EOF
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
exit;
EOF
fi
ORACLE_SID=$nsid
export ORACLE_SID
$sqlplus / as sysdba @verifyDatabase.sql

retval=$?
if [ -e $verifyDatabase ]; then
#   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi
if [ "$retval" -gt "0" ]; then
   echo "********** error cloning database ***************"
   exit 1;
fi

exit 0
